package com.ums.scumspay.bigdataimport.QryFile;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * 类名 ExecUtil
 * 作者 LYC
 * 时间 2022.08.23 2022/8/23 16:17
 * 描述：
 */
public class ExecUtil {


    private static Logger logger = LoggerFactory.getLogger(ExecUtil.class);

    private  static NumberFormat numberFormat=NumberFormat.getInstance();

    public static HashMap<String, ArrayList<ArrayList<String>>> readExcel(File file, int ignoreRow) {
        if (file.getName().toLowerCase().endsWith(".xlsx")) {
            return readExcelForXlsx(file, ignoreRow);
        } else if (file.getName().toLowerCase().endsWith(".xls")) {
            return readExcelForXls(file, ignoreRow);
        }
        return null;
    }

    public static HashMap<String, ArrayList<ArrayList<String>>> readExcel(InputStream in, int ignoreRow) {
        return readExcelForXlsx(in, ignoreRow);

    }

    static {
        numberFormat.setGroupingUsed(false);
        numberFormat.setMaximumFractionDigits(6);
    }
    /**
     * 读取Excel xlsx后缀名文件数据
     *
     * @param file
     */
    private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXlsx(File file, int ignoreRow) {
        HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>();
        if (!file.exists()) {
            logger.error("{}文件不存在", file.getName());
            return null;
        }
        int rowSize = 0;
        try (BufferedInputStream in = new BufferedInputStream(new FileInputStream(file))) {
            XSSFWorkbook workbook = null;
            try {
                workbook = new XSSFWorkbook(in);
            } catch (IOException e) {
                e.printStackTrace();
            }
            XSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                ArrayList<ArrayList<String>> lists = new ArrayList<>();
                for (int rowIndex = ignoreRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    XSSFRow row = sheet.getRow(rowIndex);
                    if (null == row) {
                        continue;
                    }else {
                        int tempRowSize = row.getLastCellNum() + 1;
                        if (tempRowSize > rowSize) {
                            rowSize = tempRowSize;
                        }

                        ArrayList<String> list = new ArrayList<>();
                        int col = 0;

                        for (int colIndex = 0; colIndex <= row.getLastCellNum(); colIndex++) {
                            cell = row.getCell(colIndex);
                            String value = "";
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case Cell.CELL_TYPE_NUMERIC:
                                        value=String.valueOf(numberFormat.format(Double.valueOf(cell.getNumericCellValue())));
                                        break;
                                    case  Cell.CELL_TYPE_STRING:
                                        value = String.valueOf(cell.getStringCellValue());
                                        break;
                                    case Cell.CELL_TYPE_FORMULA:
                                        //后面使用它来执行计算公式
                                        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                        String cellFormula=cell.getCellFormula();
                                        CellValue cellValue=formulaEvaluator.evaluate(cell);
                                        System.out.println(cellFormula+"   "+cellValue);
                                        value = String.valueOf(numberFormat.format(Double.valueOf(cellValue.formatAsString())));
                                        break;
                                    case Cell.CELL_TYPE_BLANK:
                                        value = "0.000000";
                                        break;
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        value = String.valueOf(cell.getBooleanCellValue());
                                        break;
                                    case Cell.CELL_TYPE_ERROR:
                                        value = String.valueOf(cell.getErrorCellValue());
                                        break;
                                    default:
                                        value = "0.000000";
                                }
                                if (StringUtils.isNotBlank(value)) {
                                    list.add(value);
                                } else {
                                    col++;
                                }
                            }else{
                                list.add("0");
                            }
                        }
                        if (col == row.getRowNum()) {
                            continue;
                        }
                        if (list.size() > 0) {
                            lists.add(list);
                        }
                    }

                }

                map.put("sheet" + sheetIndex, lists);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }



    private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXlsx(InputStream inputStream, int ignoreRow) {
        HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>();
        int rowSize = 0;
        try (BufferedInputStream in = new BufferedInputStream(inputStream)) {
            XSSFWorkbook workbook = null;
            try {
                workbook = new XSSFWorkbook(in);
            } catch (IOException e) {
                inputStream.close();
                e.printStackTrace();
            }
            XSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                ArrayList<ArrayList<String>> lists = new ArrayList<>();
                for (int rowIndex = ignoreRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    XSSFRow row = sheet.getRow(rowIndex);

                    if (row.getCell(1) == null || row.getCell(1).getCellType() == row.getCell(1).CELL_TYPE_BLANK) {
                        continue;
                    }else {
                        int tempRowSize = row.getLastCellNum() + 1;
                        if (tempRowSize > rowSize) {
                            rowSize = tempRowSize;
                        }

                        ArrayList<String> list = new ArrayList<>();
                        int col = 0;

                        for (int colIndex = 0; colIndex <= row.getLastCellNum(); colIndex++) {
                            cell = row.getCell(colIndex);
                            String value = "";
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case Cell.CELL_TYPE_NUMERIC:
                                        value=String.valueOf(numberFormat.format(Double.valueOf(cell.getNumericCellValue())));
                                        break;
                                    case  Cell.CELL_TYPE_STRING:
                                        value = String.valueOf(cell.getStringCellValue());
                                        break;
                                    case Cell.CELL_TYPE_FORMULA:
                                        //后面使用它来执行计算公式
                                        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                        String cellFormula=cell.getCellFormula();
                                        CellValue cellValue=formulaEvaluator.evaluate(cell);
                                        System.out.println(cellFormula+"   "+cellValue);
                                        value = String.valueOf(numberFormat.format(Double.valueOf(cellValue.formatAsString())));
                                        break;
                                    case Cell.CELL_TYPE_BLANK:
                                        value = "0.000000";
                                        break;
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        value = String.valueOf(cell.getBooleanCellValue());
                                        break;
                                    case Cell.CELL_TYPE_ERROR:
                                        value = String.valueOf(cell.getErrorCellValue());
                                        break;
                                    default:
                                        value = "0.000000";
                                }
                                if (StringUtils.isNotBlank(value)) {
                                    list.add(value);
                                } else {
                                    col++;
                                }
                            }else{
                                list.add("0");
                            }
                        }
                        if (col == row.getRowNum()) {
                            System.out.println("countinue");
                            continue;
                        }
                        if (list.size() > 0) {
                            lists.add(list);
                        }
                    }

                }

                map.put("sheet" + sheetIndex, lists);
            }

        } catch (Exception e) {
            try {
                inputStream.close();
            } catch (IOException ex) {
                throw new RuntimeException(ex);
            }
            e.printStackTrace();
        }
        return map;
    }


    /**
     * 读取excel xls后缀名文件
     *
     * @param file
     * @param ignoreRow
     * @return
     */
    private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXls(File file, int ignoreRow) {
        HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>();
        if (!file.exists()) {
            logger.error("{}文件不存在", file.getName());
            return null;
        }
        int rowSize = 0;
        try {
            BufferedInputStream bufferedInputStream = new BufferedInputStream(new FileInputStream(file));
            HSSFWorkbook workbook = new HSSFWorkbook(bufferedInputStream);
            HSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                ArrayList<ArrayList<String>> lists = new ArrayList<>();
                for (int rowIndex = ignoreRow; rowIndex < sheet.getLastRowNum(); rowIndex++) {
                    HSSFRow row = sheet.getRow(rowIndex);
                    if (null == row) {
                        continue;
                    }

                    int tempRowSize = row.getLastCellNum() + 1;
                    if (tempRowSize > rowSize) {
                        rowSize = tempRowSize;
                    }
                    ArrayList<String> list = new ArrayList<>();
                    int col = 0;
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        cell = row.getCell(colIndex);
                        String value = "";
                        if (cell != null) {
//                            CellType cellType = cell.getCellType();

                            switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        value = String.valueOf(cell.getDateCellValue());
                                    } else {
                                        value = String.valueOf(new DecimalFormat("0.000000").format(cell.getNumericCellValue()));
                                    }
                                    break;
                                case Cell.CELL_TYPE_STRING:
                                    value = String.valueOf(cell.getStringCellValue());
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    value = String.valueOf(cell.getCellFormula());
                                    break;
                                case Cell.CELL_TYPE_BLANK:
                                    value = "";
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    value = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case Cell.CELL_TYPE_ERROR:
                                    value = String.valueOf(cell.getErrorCellValue());
                                    break;
                                default:
                                    value = "";
                            }
                            if (StringUtils.isNotBlank(value)) {
                                list.add(value);
                            } else {
                                col++;
                            }
                        }
                    }
                    if (col == row.getRowNum()) {
                        continue;
                    }
                    if (list.size() > 0) {
                        lists.add(list);
                    }
                }
                map.put("sheet" + sheetIndex, lists);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    public static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXls(InputStream inputStream, int ignoreRow) {
        HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>();
        int rowSize = 0;
        try {
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            HSSFWorkbook workbook = new HSSFWorkbook(bufferedInputStream);
            HSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                ArrayList<ArrayList<String>> lists = new ArrayList<>();
                for (int rowIndex = ignoreRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    HSSFRow row = sheet.getRow(rowIndex);
                    if (null == row) {
                        continue;
                    }

                    int tempRowSize = row.getLastCellNum() + 1;
                    if (tempRowSize > rowSize) {
                        rowSize = tempRowSize;
                    }
                    ArrayList<String> list = new ArrayList<>();
                    int col = 0;
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        cell = row.getCell(colIndex);
                        String value = "";
                        if (cell != null) {
//                            CellType cellType = cell.getCellType();

                            switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        value = String.valueOf(cell.getDateCellValue());
                                    } else {
                                        value = String.valueOf(new DecimalFormat("0.000000").format(cell.getNumericCellValue()));
                                    }
                                    break;
                                case Cell.CELL_TYPE_STRING:
                                    value = String.valueOf(cell.getStringCellValue());
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    value = String.valueOf(cell.getCellFormula());
                                    break;
                                case Cell.CELL_TYPE_BLANK:
                                    value = "";
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    value = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case Cell.CELL_TYPE_ERROR:
                                    value = String.valueOf(cell.getErrorCellValue());
                                    break;
                                default:
                                    value = "";
                            }
                            if (StringUtils.isNotBlank(value)) {
                                list.add(value);
                            } else {
                                col++;
                            }
                        }
                    }
                    if (col == row.getRowNum()) {
                        continue;
                    }
                    if (list.size() > 0) {
                        lists.add(list);
                    }
                }
                map.put("sheet" + sheetIndex, lists);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }
}
